/**
 * @Title: DefaultCameraPhotoDao.java
 * @Package com.sinotn.ks.node.camera.dao.jdbc
 * @Description: TODO
 * Copyright: Copyright (c) 2015
 * Company:北京信诺软通
 */
package com.sinotn.ks.node.camera.dao.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.sinotn.Page;
import com.sinotn.id.SeqUUIDGenerator;
import com.sinotn.ks.definition.ExamDataContext;
import com.sinotn.ks.node.camera.dao.CameraPhotoDao;
import com.sinotn.ks.node.camera.dao.cond.CameraPhotoCond;
import com.sinotn.ks.node.camera.dao.jdbc.extractor.CameraPhotoExtractor;
import com.sinotn.ks.node.camera.dao.jdbc.setter.CameraPhotoCondSetter;
import com.sinotn.ks.node.camera.dao.jdbc.setter.CameraPhotoDeleteMultiSetter;
import com.sinotn.ks.node.camera.util.SqlUtil;
import com.sinotn.ks.node.camera.vo.CameraPhotoVO;
import com.sinotn.ks.node.runtime.vo.AnswerPaperVO;
import com.sinotn.spring.jdbc.OneIntegerExtractor;
import com.sinotn.spring.jdbc.PageQuery;
import com.sinotn.spring.jdbc.ParameterSetter;
import com.sinotn.spring.jdbc.SqlDialect;

/**
 * @Title DefaultCameraPhotoDao.java
 * @Package com.sinotn.ks.node.camera.dao.jdbc
 * @Description
 * Copyright: Copyright (c) 2015
 * Company:北京信诺软通
 *
 * @author <a href="mailto:liyunfeng@sinotn.com">李云峰</a>
 * @date 2017年6月22日 下午10:14:27
 * @version V1.0
 */
public class DefaultCameraPhotoDao extends JdbcDaoSupport implements CameraPhotoDao {
	private String schema="ks_node_data";
	private String tableCamera = "KD_CAMERA_PHOTO";
	@Autowired
	private SqlDialect sqlDialect;
	@Autowired
	private ExamDataContext examDataContext;

	public void setSchema(String schema) {
		this.schema = schema;
	}

	@Override
	public int selectCameraPhotoCount() {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT COUNT(1) FROM ");
		sql.append(this.schema).append(this.examDataContext.getSuffix()).append(".").append(this.tableCamera);
		ParameterSetter pstm = new ParameterSetter();

		return this.getJdbcTemplate().query(sql.toString(), pstm, OneIntegerExtractor.get());
	}

	@Override
	public Page selectCameraPhotoByPage(int pageIdx, int pageSize, CameraPhotoCond cond) {
		CameraPhotoCondSetter setter = new CameraPhotoCondSetter(cond);
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT COUNT(1) FROM ");
		sql.append(this.schema).append(this.examDataContext.getSuffix()).append(".").append(this.tableCamera);

		sql.append(setter.getCameraPhotoCondSql());
		//sql.append(" AND FIXED=0 ");
		JdbcTemplate jdbcTemplate = this.getJdbcTemplate();
		int count = jdbcTemplate.query(sql.toString(), setter, OneIntegerExtractor.get());
		Page page = new Page(pageIdx, pageSize, count);
		if (count == 0) {
			page.setRows(new ArrayList<AnswerPaperVO>(1));
			return page;
		}

		CameraPhotoExtractor extractor = new CameraPhotoExtractor();
		sql = new StringBuilder();
		sql.append("SELECT ").append(extractor.getColumns()).append(" FROM ");
		sql.append(this.schema).append(this.examDataContext.getSuffix()).append(".").append(this.tableCamera);
		sql.append(setter.getCameraPhotoCondSql());

		PageQuery pageQuery = sqlDialect.getPageQuery(page.getPageIdx(), page.getPageSize(), sql.toString());
		setter.setPageQuery(pageQuery);

		List<CameraPhotoVO> list = getJdbcTemplate().query(pageQuery.getQuerySql(), setter, extractor);
		page.setRows(list);

		return page;
	}

	@Override
	public List<CameraPhotoVO> selectCameraPhotos(int pageIdx, int pageSize, CameraPhotoCond cond) {
		CameraPhotoExtractor extractor = new CameraPhotoExtractor();
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT ").append(extractor.getColumns()).append(" FROM ");
		sql.append(this.schema).append(this.examDataContext.getSuffix()).append(".").append(this.tableCamera);

		CameraPhotoCondSetter setter = new CameraPhotoCondSetter(cond);
		sql.append(setter.getCameraPhotoCondSql());
		JdbcTemplate jdbcTemplate=this.getJdbcTemplate();
		if(pageSize>0){
			PageQuery pageQuery=this.sqlDialect.getPageQuery(pageIdx, pageSize, sql.toString());
			setter.setPageQuery(pageQuery);

			return jdbcTemplate.query(pageQuery.getQuerySql(), setter, extractor);
		}else{
			return jdbcTemplate.query(sql.toString(), setter, extractor);
		}
	}

	@Override
	public CameraPhotoVO selectCameraPhotoById(String id) {
		CameraPhotoExtractor extractor = new CameraPhotoExtractor();
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT ").append(extractor.getColumns()).append(" FROM ").append(this.schema).append(this.examDataContext.getSuffix()).append(".").append(this.tableCamera);
		sb.append(" WHERE id=?");
		ParameterSetter ps = new ParameterSetter(1);
		ps.set(id);
		List<CameraPhotoVO> list = this.getJdbcTemplate().query(sb.toString(), ps, extractor);
		if (list == null || list.isEmpty()) {
			return null;
		}
		return list.get(0);
	}

	@Override
	public void insertCameraPhoto(CameraPhotoVO cameraPhotoVO) {
		CameraPhotoExtractor extractor = new CameraPhotoExtractor();
		StringBuilder sql = new StringBuilder();
		sql.append("INSERT INTO ").append(this.schema).append(this.examDataContext.getSuffix()).append(".").append(this.tableCamera);
		sql.append(" (" + extractor.getColumns() + ")");
		sql.append(" VALUES (" + SqlUtil.getPlaceholder(extractor.getColumns()) + ")");
		ParameterSetter pstm = new ParameterSetter();
		if (cameraPhotoVO.getId() == null || cameraPhotoVO.getId().length() <= 0) {
			cameraPhotoVO.setId(SeqUUIDGenerator.genSequenceUUID(false));
		}
		/*
		 * 按照该顺序进行设值
		 * FIXED,SIMILAR,FACE_COUNT,STATE,phase,INVIGILATOR_NAME,PHOTO_PATH,ID,CREATE_TIME,
		 * EXAMINEE_ID,INVIGILATOR_PHONE,ANSWER_PAPER_ID,EXAM_INFO_ID,REMARK
		 */
		pstm.set(cameraPhotoVO.getFixed());
		pstm.set(cameraPhotoVO.getSimilar());
		pstm.set(cameraPhotoVO.getFaceCount());
		pstm.set(cameraPhotoVO.getState());
		pstm.set(cameraPhotoVO.getPhase());
		pstm.set(cameraPhotoVO.getInvigilatorName());
		pstm.set(cameraPhotoVO.getPhotoPath());
		pstm.set(cameraPhotoVO.getId());
		pstm.set(cameraPhotoVO.getCreateTime().getTime());
		pstm.set(cameraPhotoVO.getExamineeId());
		pstm.set(cameraPhotoVO.getInvigilatorPhone());
		pstm.set(cameraPhotoVO.getAnswerPaperId());
		pstm.set(cameraPhotoVO.getExamInfoId());
		pstm.set(cameraPhotoVO.getRemark());
		pstm.set(cameraPhotoVO.getRoomId());
		pstm.set(cameraPhotoVO.getExamineeName());

		this.getJdbcTemplate().update(sql.toString(), pstm);
	}

	@Override
	public void updateCameraPhoto(CameraPhotoVO cameraPhotoVO) {
		StringBuilder sql = new StringBuilder();
		sql.append("UPDATE ").append(this.schema).append(this.examDataContext.getSuffix()).append(".").append(this.tableCamera);
		sql.append(" SET FIXED=?,SIMILAR=?,FACE_COUNT=?,STATE=?,phase=?,INVIGILATOR_NAME=?,PHOTO_PATH=?,CREATE_TIME=?,");
		sql.append(" EXAMINEE_ID=?,INVIGILATOR_PHONE=?,EXAM_INFO_ID=?,REMARK=?,ROOM_ID=?,EXAMINEE_NAME=?");
		sql.append(" WHERE ID=?");
		ParameterSetter pstm = new ParameterSetter();

		pstm.set(cameraPhotoVO.getFixed());
		pstm.set(cameraPhotoVO.getSimilar());
		pstm.set(cameraPhotoVO.getFaceCount());
		pstm.set(cameraPhotoVO.getState());
		pstm.set(cameraPhotoVO.getPhase());
		pstm.set(cameraPhotoVO.getInvigilatorName());
		pstm.set(cameraPhotoVO.getPhotoPath());
		pstm.set(cameraPhotoVO.getCreateTime().getTime());
		pstm.set(cameraPhotoVO.getExamineeId());
		pstm.set(cameraPhotoVO.getInvigilatorPhone());
		pstm.set(cameraPhotoVO.getExamInfoId());
		pstm.set(cameraPhotoVO.getRemark());
		pstm.set(cameraPhotoVO.getRoomId());
		pstm.set(cameraPhotoVO.getExamineeName());

		pstm.set(cameraPhotoVO.getId());
		this.getJdbcTemplate().update(sql.toString(), pstm);
	}
	
	public int updateAnalyse(String id, byte state, int faceCount,int similar){
		StringBuilder sql = new StringBuilder();
		sql.append("UPDATE ").append(this.schema).append(this.examDataContext.getSuffix()).append(".").append(this.tableCamera);
		sql.append(" SET STATE=?");
		ParameterSetter pstm = new ParameterSetter(4);

		pstm.set(state);
		if(faceCount>=0){
			sql.append(",FACE_COUNT=?");
			pstm.set(faceCount);
		}
		if(similar>=0){
			sql.append(",SIMILAR=?");
			pstm.set(similar);
		}
		sql.append(" WHERE ID=?");
		pstm.set(id);
		return this.getJdbcTemplate().update(sql.toString(), pstm);
	}

	@Override
	public void deleteCameraPhotoById(String id) {
		StringBuilder sql = new StringBuilder(128);
		sql.append("DELETE FROM ").append(this.schema).append(this.examDataContext.getSuffix()).append(".").append(this.tableCamera);
		sql.append(" WHERE ID=?");
		ParameterSetter ps = new ParameterSetter();
		ps.set(id);
		this.getJdbcTemplate().update(sql.toString(), ps);
	}

	@Override
	public void deleteMultiPhotos(List<CameraPhotoVO> photoList) {
		StringBuilder sql = new StringBuilder(128);
		sql.append("DELETE FROM ").append(this.schema).append(this.examDataContext.getSuffix()).append(".").append(this.tableCamera);
		sql.append(" WHERE ID=?");

		JdbcTemplate jdbcTemplate = this.getJdbcTemplate();
		CameraPhotoDeleteMultiSetter setter = new CameraPhotoDeleteMultiSetter(photoList);
		jdbcTemplate.batchUpdate(sql.toString(), setter);
	}

	@Override
	public Map<String, Integer> selectWarningPhotos(CameraPhotoCond cond) {
		CameraPhotoCondSetter setter = new CameraPhotoCondSetter(cond);
		StringBuilder sql = new StringBuilder(128);
		sql.append("SELECT ROOM_ID, COUNT(*) 'TOTAL' FROM ").append(this.schema).append(this.examDataContext.getSuffix()).append(".").append(this.tableCamera);
		sql.append(setter.getCameraPhotoCondSql());
		sql.append(" GROUP BY ROOM_ID");

		Map<String, Integer> map = this.getJdbcTemplate().query(sql.toString(), setter, new ResultSetExtractor<Map<String, Integer>>() {
			@Override
			public Map<String, Integer> extractData(ResultSet rs) throws SQLException, DataAccessException {
				Map<String, Integer> tmp = new HashMap<String, Integer>();
				while (rs.next()) {
					tmp.put(rs.getString("ROOM_ID"), rs.getInt("TOTAL"));
				}
				return tmp;
			}

		});
		return map;
	}

	@Override
	public List<CameraPhotoVO> selectNormalPhotoInExam(String examineeId, String roomId) {
		CameraPhotoExtractor extractor = new CameraPhotoExtractor();
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT ").append(extractor.getColumns("m")).append(" FROM ").append(this.schema).append(this.examDataContext.getSuffix()).append(".").append(this.tableCamera).append(" m");
		sb.append(" WHERE m.CREATE_TIME IN (");
		sb.append(" SELECT MAX(CREATE_TIME) FROM ").append(this.schema).append(this.examDataContext.getSuffix()).append(".").append(this.tableCamera);
		sb.append(" WHERE PHASE='0'").append(" AND ROOM_ID='").append(roomId).append("' AND EXAMINEE_ID='").append(examineeId).append("')");
		sb.append(" AND m.ROOM_ID='").append(roomId).append("' AND m.EXAMINEE_ID='").append(examineeId).append("'");
		sb.append(" UNION ");
		sb.append(" SELECT ").append(extractor.getColumns("n")).append(" FROM ").append(this.schema).append(this.examDataContext.getSuffix()).append(".").append(this.tableCamera).append(" n");
		sb.append(" WHERE n.CREATE_TIME IN (");
		sb.append(" SELECT MAX(CREATE_TIME) FROM ").append(this.schema).append(this.examDataContext.getSuffix()).append(".").append(this.tableCamera);
		sb.append(" WHERE PHASE='1'").append(" AND ROOM_ID='").append(roomId).append("' AND EXAMINEE_ID='").append(examineeId).append("')");
		sb.append(" AND n.ROOM_ID='").append(roomId).append("' AND n.EXAMINEE_ID='").append(examineeId).append("'");
		sb.append(" UNION ");
		sb.append(" SELECT ").append(extractor.getColumns("k")).append(" FROM ").append(this.schema).append(this.examDataContext.getSuffix()).append(".").append(this.tableCamera).append(" k");
		sb.append(" WHERE k.CREATE_TIME IN (");
		sb.append(" SELECT MAX(CREATE_TIME) FROM ").append(this.schema).append(this.examDataContext.getSuffix()).append(".").append(this.tableCamera);
		sb.append(" WHERE PHASE='2'").append(" AND ROOM_ID='").append(roomId).append("' AND EXAMINEE_ID='").append(examineeId).append("')");
		sb.append(" AND k.ROOM_ID='").append(roomId).append("' AND k.EXAMINEE_ID='").append(examineeId).append("'");
		ParameterSetter ps = new ParameterSetter();
		return this.getJdbcTemplate().query(sb.toString(), ps, extractor);
	}

	@Override
	public int selectCount(CameraPhotoCond cond) {
		CameraPhotoCondSetter setter = new CameraPhotoCondSetter(cond);
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT COUNT(1) FROM ");
		sql.append(this.schema).append(this.examDataContext.getSuffix()).append(".").append(this.tableCamera);

		sql.append(setter.getCameraPhotoCondSql());
		JdbcTemplate jdbcTemplate = this.getJdbcTemplate();
		return jdbcTemplate.query(sql.toString(), setter, OneIntegerExtractor.get());
	}
}
